Week 03 Assignment Covid

New types of data and new data science technologies enable new research. These new technologies are technologies such as the ability to combine existing data or the ability to generate synthetic data from existing knowledge. This week casus is based on such research. Data is generated by Synthea's COVID-19 module. The data was constructed using three peer-reviewed publications published in the early stages of the global pandemic, when less was known, along with emerging resources, data, publications, and clinical knowledge. The simulation outputs synthetic Electronic Health Records (EHR), including the daily consumption of Personal Protective Equipment (PPE) and other medical devices and supplies. The Data is stored in separate tables to avoid redundancy, with as a concequence that tables need to be combined and reorganized in dataframes for analysing purpose.

Keywords: merge data, subset data, clean data, generate data

You will learn about combining data with pandas and numpy and you will learn to visualize with bokeh. Concretely, you will preprocess the partly Synthetic Covid data in an appropiate format in order to conduct statistical and visual analysis. Learning objectives

• Combine multiple data sources for analysis
• Read, inspect, clean, reshape data
• Visualize data using bokeh
• Maintain development environment
• Apply coding standards and FAIR principles
• Reshape the dataset into a format suitable for visual and statistical analysis
• Use widgets to make the plot interactive
• Use GIS libraries to plot geographical data

Tutorials about combining data: https://github.com/fenna/BFVM22PROG1/blob/main/tutorials/tutorial_combine_data.ipynb

study case combining data:https://github.com/fenna/BFVM22PROG1/blob/main/study_cases/adults_who_binge_drank_in_hot_towns.ipynb

Please add the topics you want to learn about here: https://padlet.com/ffeenstra1/kzh2chaqleq3iovu

Your job is to visualize the lab values taken for COVID-19 patients of survived versus not survived patients.

The assignment consists of 6 parts:

• part 1: load the data
  • Exercise 1.1
• part 2: data wrangling
  • Exercise 2.1
• part 3: more wrangling
  • Exercise 3.1
• part 4: plot the data
  • Exercise 4.1
• part 5: plot patient location
  • Exercise 5.1

Part 1 and 4 are mandatory, part 5 is optional (bonus) Mind you that you cannot copy code without referencing the code. If you copy code you need to be able to explain your code verbally and you will not get the full score.

About the data

The data is generated by Synthea's COVID-19 module. The data was constructed using three peer-reviewed publications published in the early stages of the global pandemic, when less was known, along with emerging resources, data, publications, and clinical knowledge. The simulation outputs synthetic Electronic Health Records (EHR), including the daily consumption of Personal Protective Equipment (PPE) and other medical devices and supplies. For this assignment the conditions, patients, observations, careplans and encounters table will be used. The Data is stored in separate tables to avoid redundancy, with as a concequence that tables need to be combined and reorganized in dataframes for analysing purpose.

Source: Walonoski J, Klaus S, Granger E, Hall D, Gregorowicz A, Neyarapally G, Watson A, Eastman J. Synthea™ Novel coronavirus (COVID-19) model and synthetic data set. Intelligence-Based Medicine. 2020 Nov;1:100007. https://doi.org/10.1016/j.ibmed.2020.100007

Please download the data

Covid Patients

Patients are considered Covid patients if they are identified with CODE 840539006

Survivors

Patients that had covid and where tested negative after isolation have tested code 94531-1, SARS-CoV-2 RNA Pnl Resp NAA+probe (covid-sars test) + a value of Not detected (qualifier value). These patients are considered to be survived covid patients.

Non-Survivors

Patients that did not survived Covid have a DEATHDATE which is not null.

Lab values COVID-19 patients

Patients are monitored for blood and heart conditions once they are admitted in Hospital or under treatment. The lab values of interest are as follow:

• 48065-7 Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma
• 26881-3 Interleukin 6 [Mass/volume] in Serum or Plasma
• 2276-4 Ferritin [Mass/volume] in Serum or Plasma
• 89579-7 Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method
• 731-0 Lymphocytes [#/volume] in Blood by Automated count
• 14804-9 Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reaction

---

Part 1: Load the data (20 pt)

Instructions: Load the data of the following files. Preferably we read the data not with a hard coded data path but using a config file. See https://fennaf.gitbook.io/bfvm22prog1/data-processing/configuration-files/yaml

• conditions.csv
• patients.csv
• observations.csv
• careplans.csv
• encounters.csv

Get yourself familiar with the data. Create some meaningful overviews. Answer the following questions

1. How many patients are there
2. How many covid-patients are there
3. How many patients do have a 'Hospital admission for isolation' encounter

Hints

• use a unique dataframe for each file, use a meaningful name
• pandas.read_csv() method can be used to read a csv file
• pandas.DataFrame.head() method is often used to inspect the dataframe
• .unique() returns a list of unique values of a column

1.1 Code your solution

import yaml
import pandas as pd
import numpy as np

files = ['conditions.csv', 'patients.csv', 'observations.csv', 'careplans.csv', 'encounters.csv']


with open("config.yaml", 'r') as config:
    data3 = yaml.safe_load(config)['data3']

df_dict = {
    'conditions': pd.read_csv(data3 + files[0]), 
    'patients': pd.read_csv(data3 + files[1]), 
    'observations': pd.read_csv(data3 + files[2]), 
    'careplans': pd.read_csv(data3 + files[3]), 
    'encounters': pd.read_csv(data3 + files[4])
}

num_patients = df_dict["patients"]
covid_patients =  df_dict["conditions"][df_dict["conditions"]["CODE"] == 840539006]
num_admitted = df_dict["observations"][df_dict["observations"]['CODE'].isin(['48065-7', '26881-3', '2276-4','89579-7', '731-0', '14804-9'])]['PATIENT'].unique()
num_died = df_dict["patients"][df_dict["patients"]['DEATHDATE'].notnull()]
num_patients

	Id	BIRTHDATE	DEATHDATE	SSN	DRIVERS	PASSPORT	PREFIX	FIRST	LAST	SUFFIX	...	BIRTHPLACE	ADDRESS	CITY	STATE	COUNTY	ZIP	LAT	LON	HEALTHCARE_EXPENSES	HEALTHCARE_COVERAGE
0	f0f3bc8d-ef38-49ce-a2bd-dfdda982b271	2017-08-24	NaN	999-68-6630	NaN	NaN	NaN	Jacinto644	Kris249	NaN	...	Beverly Massachusetts US	888 Hickle Ferry Suite 38	Springfield	Massachusetts	Hampden County	1106.0	42.151961	-72.598959	8446.49	1499.08
1	067318a4-db8f-447f-8b6e-f2f61e9baaa5	2016-08-01	NaN	999-15-5895	NaN	NaN	NaN	Alva958	Krajcik437	NaN	...	Boston Massachusetts US	1048 Skiles Trailer	Walpole	Massachusetts	Norfolk County	2081.0	42.177370	-71.281353	89893.40	1845.72
2	ae9efba3-ddc4-43f9-a781-f72019388548	1992-06-30	NaN	999-27-3385	S99971451	X53218815X	Mr.	Jayson808	Fadel536	NaN	...	Springfield Massachusetts US	1056 Harris Lane Suite 70	Chicopee	Massachusetts	Hampden County	1020.0	42.181642	-72.608842	577445.86	3528.84
3	199c586f-af16-4091-9998-ee4cfc02ee7a	2004-01-09	NaN	999-73-2461	S99956432	NaN	NaN	Jimmie93	Harris789	NaN	...	Worcester Massachusetts US	201 Mitchell Lodge Unit 67	Pembroke	Massachusetts	Plymouth County	NaN	42.075292	-70.757035	336701.72	2705.64
4	353016ea-a0ff-4154-85bb-1cf8b6cedf20	1996-11-15	NaN	999-60-7372	S99917327	X58903159X	Mr.	Gregorio366	Auer97	NaN	...	Patras Achaea GR	1050 Lindgren Extension Apt 38	Boston	Massachusetts	Suffolk County	2135.0	42.352434	-71.028610	484076.34	3043.04
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
12347	eec72396-d10e-4b8e-8fb6-25fc1e396031	1962-08-17	NaN	999-52-5112	S99981543	X65879689X	Mrs.	Althea11	O'Hara248	NaN	...	Delhi Delhi IN	682 Koss Trafficway Apt 65	Wellesley	Massachusetts	Norfolk County	2457.0	42.294175	-71.259364	1332256.08	7157.90
12348	3daf2b90-c439-469f-bb44-fb6109c6a8a7	1918-10-20	1999-11-01	999-25-1153	S99951005	X1374154X	Mrs.	Tarah156	Shields502	NaN	...	New Bedford Massachusetts US	308 Huels Grove Apt 18	Waltham	Massachusetts	Middlesex County	2453.0	42.372663	-71.209053	1557234.62	27214.48
12349	cb03d88e-7f08-46a1-be78-70d9fe8262a7	1918-10-20	2009-08-07	999-41-7538	S99938113	X29702266X	Mrs.	Penny812	Pacocha935	NaN	...	Marshfield Massachusetts US	349 Breitenberg Walk Suite 26	Waltham	Massachusetts	Middlesex County	2452.0	42.396186	-71.217928	1347930.08	26001.41
12350	435be43f-cb57-442f-aeb2-51018b030ed0	1918-10-20	NaN	999-26-4571	S99995011	X51104300X	Ms.	Cherlyn665	Quitzon246	NaN	...	Holyoke Massachusetts US	237 Miller Avenue	Waltham	Massachusetts	Middlesex County	2452.0	42.366768	-71.196715	1585631.29	32006.97
12351	2712205f-755e-4897-acb3-926895b7d635	1941-02-11	NaN	999-57-4019	S99932976	X25566175X	Mr.	Emory494	Turcotte120	NaN	...	Medford Massachusetts US	595 Zulauf Parade Suite 56	Bourne	Massachusetts	Barnstable County	2532.0	41.721661	-70.587214	1689239.58	790451.35

12352 rows × 25 columns

1.2 Test your solution

The following function needs to be called. You can use this as a test. There are however more meaningful overviews you can create.

def part1(num_pat, num_cov, num_admitted, num_died):
    print(f'There are {num_pat} patients in total')
    print(f'There are {num_cov} covid patients')
    print(f'There are {num_admitted} admitted patients')
    print(f'{num_died} patients died')

part1(len(num_patients), len(covid_patients), len(num_admitted), len(num_died))   

There are 12352 patients in total
There are 8820 covid patients
There are 1867 admitted patients
2352 patients died

Expected outcome

There are 12352 patients in total There are 8820 covid patients There are 1867 admitted patients 2352 patients died

---

Part 2: Data Wrangling: set up the dataframe (30 pt)

In this part we are going to combine data to create a dataframe with values of interest for the lab values analysis.

We would like a dataframe containing the following information per record (only Covid patients!!!)

• PATIENT - the ID of the covid patient
• days - the number of days the patient is under observation
• CODE-Y - the code of the observation
• VALUE - the lab value of the observation

where only the following observation codes needs to be selected:

• 48065-7 Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma
• 26881-3 Interleukin 6 [Mass/volume] in Serum or Plasma
• 2276-4 Ferritin [Mass/volume] in Serum or Plasma
• 89579-7 Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method
• 731-0 Lymphocytes [#/volume] in Blood by Automated count
• 14804-9 Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reaction

The days information is not primarely available and needs to be calculated by substracting observation DATE - START.

An example of such a dataframe is given below:

days CODE-Y VALUE UNITS PATIENT 00079a57-24a8-430f-b4f8-a1cf34f90060 0.0 14804-9 241.8 U/L 00079a57-24a8-430f-b4f8-a1cf34f90060 0.0 2276-4 489.5 ug/L 00079a57-24a8-430f-b4f8-a1cf34f90060 0.0 48065-7 0.4 ug/mL 00079a57-24a8-430f-b4f8-a1cf34f90060 0.0 731-0 1.1 10*3/uL 00079a57-24a8-430f-b4f8-a1cf34f90060 0.0 89579-7 3.4 pg/mL 00079a57-24a8-430f-b4f8-a1cf34f90060 1.0 731-0 1.1 10*3/uL 00079a57-24a8-430f-b4f8-a1cf34f90060 2.0 14804-9 233.7 U/L 00079a57-24a8-430f-b4f8-a1cf34f90060 2.0 2276-4 497.3 ug/L 00079a57-24a8-430f-b4f8-a1cf34f90060 2.0 48065-7 0.3 ug/mL 00079a57-24a8-430f-b4f8-a1cf34f90060 2.0 731-0 1.0 10*3/uL

#Possible approach:

#Select all the patients with covid from the conditions table
#Combine conditions table (only covid patients) with the patient table into a covid_patient table
#select the only the relevant lab observations from the observations table into a lab_obs table
#merge the covid_patient table with the lab_obs table into a covid_patients_obs table
#clean the covid_patients_obs table (rename columns, select only relevant columns, sort, typecast, add days column)

Hints

• you can use pandas.DataFrame.merge() to merge dataframes
• df = df[(df.CODE == condition1 | df.CODE == condition1 )] selects rows with CODE of 2 conditional values
• df.DATE - df.START return days if DATE and START are datetime format
• pd.to_datetime() can be used to typecast to datetime

2.1 Code your solution

#YOUR CODE HERE

covid = df_dict["conditions"][df_dict["conditions"]["CODE"] == 840539006]
patients = df_dict["patients"]

observations = df_dict["observations"][df_dict["observations"]['CODE'].isin(['48065-7', '26881-3', '2276-4','89579-7', '731-0', '14804-9'])]


lab_obs = pd.merge(observations, covid, on='PATIENT', how="inner")

lab_obs['DAYS'] = (pd.to_datetime(lab_obs['DATE']) - pd.to_datetime(lab_obs['START'])).astype("string").str[0:2].astype("int")


clean_lab_obs = lab_obs[["PATIENT", "DAYS", "CODE_x", "VALUE", "UNITS"]]

clean_lab_obs['VALUE'] = pd.to_numeric(clean_lab_obs['VALUE'])
clean_lab_obs['DAYS'].max()

/tmp/ipykernel_253553/3619464563.py:16: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  clean_lab_obs['VALUE'] = pd.to_numeric(clean_lab_obs['VALUE'])

26

---

Part 3: Data Wrangling, split into survived and not survived (10 pt)

Now we have the required data we would like to split the data into survived and not survived. First we fetch all the ids of the survived and deceased patients. We can use these ids to select the records of the survived patients and the patients that did not survived

Your job is to split the data into survived and not survived records. There are multiple ways to do this. One way is the .isin() method

#the following code is given, RUN THIS CELL
#get survived and deceased ids
care_plans = df_dict['careplans']
observations = df_dict['observations']
patients = df_dict['patients']
completed_isolation_patients = care_plans[(care_plans.CODE == 736376001) & (care_plans.STOP.notna()) \
                                          & (care_plans.REASONCODE == 840539006)].PATIENT
negative_covid_patient_ids = observations[(observations.CODE == '94531-1') \
                                          & (observations.VALUE == 'Not detected (qualifier value)')].PATIENT.unique()
survivor_ids = np.union1d(completed_isolation_patients, negative_covid_patient_ids)
deceased_ids = patients[patients.DEATHDATE.notna()].Id

3.1 Code your solution

#YOUR CODE HERE
survived = clean_lab_obs[clean_lab_obs["PATIENT"].isin(survivor_ids)]
dead = clean_lab_obs[clean_lab_obs["PATIENT"].isin(deceased_ids)]

3.2 Test your solution

def test3(survived, died):
    print(f'patients records survived: {survived}, patients records deceased {died}')
#call the test3

test3(len(survived), len(dead))

patients records survived: 57303, patients records deceased 16793

Expected outcome

patients records survived: 57303, patients records deceased 16793

---

Part 4: Plot the data (20 pt)

Create plots with the lab data, for each code one plot. Separate the survivors and the deceased by color. An example of such a plot is given below. You can create 6 plots in one grid (for each code one plot) or use a widget (for instance a drop down menu widget) to select a lab CODE. Plot on the x-axis the days, on the y-axis the VALUE. Use proper labels, titles and legends.

4.1 Code your solution

#YOUR CODE HERE
from bokeh.io import output_notebook
from bokeh.plotting import figure, show
from bokeh.layouts import gridplot
from bokeh.tile_providers import CARTODBPOSITRON, get_provider
output_notebook()

Loading BokehJS ...

def make_plot(code, title):
    surv = survived[survived["CODE_x"] == code]
    death = dead[dead["CODE_x"] == code]
    
    p = figure(title = title)
    p.circle(x=surv["DAYS"], y=surv["VALUE"], color = "green", legend_label='survived')
    p.circle(x=death["DAYS"], y=death["VALUE"], color = "red", legend_label='died')
    p.legend.location = "top_left"
    p.legend.title = "observations"
    
    
    return p

def create_plots():
    plots = []
    observations = {
        '48065-7': 'Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma',
        '26881-3': 'Interleukin 6 [Mass/volume] in Serum or Plasma',
        '2276-4': 'Ferritin [Mass/volume] in Serum or Plasma',
        '89579-7': 'Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method',
        '731-0': 'Lymphocytes [/volume] in Blood by Automated count',
        '14804-9': 'Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reaction'
    }
    for code, title in observations.items():
        plots.append(make_plot(code, title))
    return plots
graphs = create_plots()
show(gridplot(graphs, ncols=2, width=450, height=300))

Part 5: Plot the location of the patients (10 pt)

This is a bonus part. Can you plot the patients location on a map? See also https://docs.bokeh.org/en/latest/docs/user_guide/geo.html

You can use either package folium or geopandas. You need the Latitude and Longitude information from the patient tabel

5.1 Code your solution

# Define function to switch from lat/long to mercator coordinates
def coordinates(x, y):
    
    lat = x
    lon = y
    
    r_major = 6378137.000
    x = r_major * np.radians(lon)
    scale = x/lon
    y = 180.0/np.pi * np.log(np.tan(np.pi/4.0 + 
        lat * (np.pi/180.0)/2.0)) * scale
    return (x, y)

# Define coord as tuple (lat,long)
df_dict['patients']['coordinates'] = list(zip(df_dict['patients']['LAT'], df_dict['patients']['LON']))
# Obtain list of mercator coordinates
mercators = [coordinates(x, y) for x, y in df_dict['patients']['coordinates'] ]

# Create mercator column in our df
df_dict['patients']['mercator'] = mercators
# Split that column out into two separate columns - mercator_x and mercator_y
df_dict['patients'][['mercator_x', 'mercator_y']] = df_dict['patients']['mercator'].apply(pd.Series)
source = df_dict['patients']

#create figure
p = figure(x_axis_type="mercator", y_axis_type="mercator", x_axis_label = 'Longitude', y_axis_label = 'Latitude')
#add map
tile_provider = get_provider(CARTODBPOSITRON)
p.add_tile(tile_provider)

p.circle(x = 'mercator_x', y = 'mercator_y', source=source, size=3, fill_alpha = 0.7)
show(p)